﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace APP.HELPER.Utils.Excel
{
    public class ExcelImport
    {
        public static DataTable ReadExcelContents(string fileName)
        {
            DataTable dataTable = ExcelImport.ReadSheets(fileName);
            return ExcelImport.ReadExcelContents(fileName, dataTable.Rows[0]["TABLE_NAME"].ToString());
        }
        public static DataTable ReadExcelContents(string fileName, string SheetName)
        {
            DataTable result;
            try
            {
                OleDbConnection oleDbConnection = new OleDbConnection();
                if (fileName.Substring(fileName.Length - 3) == "xls")
                {
                    oleDbConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;\r\n                        Extended Properties='Excel 8.0;HDR=No;IMEX=1';Data Source=" + fileName);
                }
                else
                {
                    oleDbConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fileName + "';Extended Properties= 'Excel 8.0;HDR=No;IMEX=1'");
                }
                string cmdText = "Select * FROM [" + SheetName + "]";
                oleDbConnection.Open();
                OleDbCommand selectCommand = new OleDbCommand(cmdText, oleDbConnection);
                OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter();
                oleDbDataAdapter.SelectCommand = selectCommand;
                DataSet dataSet = new DataSet();
                oleDbDataAdapter.Fill(dataSet);
                DataTable dataTable = dataSet.Tables[0];
                oleDbConnection.Close();
                result = dataTable;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Chương trình không thể đọc tập tin này. " + ex.Message, "Thông Báo", MessageBoxButtons.OK, MessageBoxIcon.Hand);
                result = null;
            }
            return result;
        }
        public static DataTable ReadSheets(string fileName)
        {
            DataTable result;
            try
            {
                OleDbConnection oleDbConnection = new OleDbConnection();
                if (fileName.Substring(fileName.Length - 3) == "xls")
                {
                    oleDbConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;\r\n                        Extended Properties='Excel 8.0;HDR=No;IMEX=1';Data Source=" + fileName);
                }
                else
                {
                    oleDbConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fileName + "';Extended Properties= 'Excel 8.0;HDR=No;IMEX=1'");
                }
                oleDbConnection.Open();
                DataTable dataTable = new DataTable();
                dataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                oleDbConnection.Close();
                result = dataTable;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Chương trình không thể đọc tập tin này. " + ex.Message, "Thông Báo", MessageBoxButtons.OK, MessageBoxIcon.Hand);
                result = null;
            }
            return result;
        }
    }
}
